﻿using Dapper;
using System;
using System.Collections.Generic;
using System.Data;
using System.Text;
using System.Threading.Tasks;
using BCCommon;
using BCEntity.TMS.LearningTrain.DriverLearningTrainRecord;
using BCCommon.TMS.Driver;

namespace BCData.TMS.LearningTrain.DriverLearningTrainRecord
{
    public class DriverLearningTrainRecordData : IDriverLearningTrainRecordData
    {
        private readonly IDatabaseContext database;
        private readonly string QUERY = @"SELECT
		                                    DriverLearningTrainRecordId,
		                                    DriverId,
		                                    PendingLearningVehicleId,
		                                    VehicleTrainingScheduleRecordId,
		                                    Postion_X,
		                                    Postion_Y,
		                                    PostionAddress,
		                                    PhotoAddress,
		                                    CreateTime
                                        FROM
                                            DriverLearningTrainRecord ";

        private readonly string QUERY_VIEW = @" SELECT
		                                    A.DriverLearningTrainRecordId,
		                                    A.DriverId,
		                                    A.PendingLearningVehicleId,
		                                    A.VehicleTrainingScheduleRecordId,
		                                    A.Postion_X,
		                                    A.Postion_Y,
		                                    A.PostionAddress,
		                                    A.PhotoAddress,
		                                    A.CreateTime,
											E.VehicleNo,
											F.UserName AS DriverName,
											F.Phone,
										    D.TrainingSiteConfigId,
											D.TrainingSiteName,
											D.TrainingSiteAddress,
											C.TrainingPeriods,
											C.TrainingStartDate,
											C.TrainingEndDate,
											C.TrainingTeacher,
											F.Role,
                                            E.IsLearnTrainPassed,
                                            E.LearnTrainPassedTime
                                        FROM
                                            DriverLearningTrainRecord A
                                            INNER JOIN PendingLearningVehicle B ON A.DriverId=B.DriverId
                                            INNER JOIN VehicleTrainingScheduleRecord C ON B.VehicleTrainingScheduleRecordId=C.VehicleTrainingScheduleRecordId
                                            INNER JOIN TrainingSiteConfig D ON D.TrainingSiteConfigId=C.TrainingSiteConfigId
                                            INNER JOIN driver E ON A.DriverId=E.DriverId
                                            INNER JOIN `user` F ON F.UserId=E.UserId ";

        #region 构造函数
        public DriverLearningTrainRecordData(IDatabaseContext database)
        {
            this.database = database;
        }
        #endregion

        #region InsertAsync
        /// <summary>
        /// 添加新司机安全培训记录纪录
        /// </summary>
        /// <param name="entity"></param>
        /// <returns></returns>
        public async Task<DriverLearningTrainRecordEntity> InsertAsync(DriverLearningTrainRecordEntity entity)
        {
            return await this.database.Master.InsertAsync(entity, database.Transaction);
        }
        #endregion

        #region updateAsync
        /// <summary>
        /// 修改司机安全培训记录记录
        /// </summary>
        /// <param name="entity"></param>
        /// <returns></returns>
        public async Task<DriverLearningTrainRecordEntity> UpdateAsync(DriverLearningTrainRecordEntity entity)
        {
            return await this.database.Master.UpdateAsync(entity, database.Transaction);
        }
        #endregion 

        #region Delete
        /// <summary>
        /// 删除司机安全培训记录记录
        /// </summary>
        /// <param name="driverLearningTrainRecordId"></param>
        /// <returns></returns>
        public async Task<bool> DeleteAsync(long driverLearningTrainRecordId)
        {
            string sql = "DELETE FROM DriverLearningTrainRecord WHERE DriverLearningTrainRecordId = ?driverLearningTrainRecordId";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?DriverLearningTrainRecordId", driverLearningTrainRecordId, DbType.Int64);
            return await this.database.Master.ExecuteAsync(sql, parameters, database.Transaction) > 0;
        }
        #endregion

        #region GetById
        /// <summary>
        /// 获取司机安全培训记录记录
        /// </summary>
        /// <param name="driverLearningTrainRecordId"></param>
        /// <returns></returns>
        public async Task<DriverLearningTrainRecordEntity> GetEntityAsync(long driverLearningTrainRecordId)
        {
            string sql = QUERY + " WHERE  DriverLearningTrainRecordId = ?driverLearningTrainRecordId";

            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?DriverLearningTrainRecordId", driverLearningTrainRecordId, DbType.Int64);
            return await this.database.Slave.QueryEntityAsync<DriverLearningTrainRecordEntity>(sql, parameters);
        }
        #endregion

        #region GetListAsync 查询司机安全培训记录记录
        public async Task<(IEnumerable<DriverLearningTrainRecordViewEntity>, int)> GetListAsync(long agentRecordId,string driverName,
             DriverRoleType? driverRoleType,string vehicleNo, string phone,long? trainingSiteConfigId,DateTime? trainingStartDate,
             DateTime? trainingEndDate,DateTime? learnTrainPassedStartDate,DateTime? learnTrainPassedEndDate,
             DateTime? learnTrainSignStartDate, DateTime? learnTrainSignEndDate,
             int pageIndex, int pageSize)
        {

            DynamicParameters parameters = new DynamicParameters();
            //查询条件
            List<string> wheres = new List<string>();
            wheres.Add(" C.AgentRecordId = ?AgentRecordId ");
            parameters.Add("?AgentRecordId", agentRecordId, DbType.Int64);
            if (!string.IsNullOrWhiteSpace(driverName))
            {
                wheres.Add(" F.UserName LIKE ?UserName ");
                parameters.Add("?UserName", $"%{driverName}%", DbType.AnsiString);
            }
            if (driverRoleType.HasValue)
            {
                wheres.Add(" F.Role=?Role ");
                parameters.Add("?Role", driverRoleType.Value.ToString(), DbType.AnsiString);
            }
            if (!string.IsNullOrWhiteSpace(phone))
            {
                wheres.Add(" F.Phone=?Phone ");
                parameters.Add("?Phone", phone, DbType.AnsiString);
            }
            if (!string.IsNullOrWhiteSpace(vehicleNo))
            {
                wheres.Add(" E.VehicleNo=?VehicleNo ");
                parameters.Add("?VehicleNo", vehicleNo, DbType.AnsiString);
            }
            if (trainingSiteConfigId.HasValue)
            {
                wheres.Add(" D.TrainingSiteConfigId=?TrainingSiteConfigId ");
                parameters.Add("?TrainingSiteConfigId", trainingSiteConfigId.Value, DbType.Int64);
            }
            if (trainingStartDate.HasValue)
            {
                wheres.Add(" C.TrainingStartDate>=?TrainingStartDate ");
                parameters.Add("?TrainingStartDate", trainingStartDate.Value.Date, DbType.Date);
            }
            if (trainingEndDate.HasValue)
            {
                wheres.Add(" C.TrainingEndDate<?TrainingEndDate ");
                parameters.Add("?TrainingEndDate", trainingEndDate.Value.AddDays(1).Date, DbType.Date);
            }

            if (learnTrainSignStartDate.HasValue)
            {
                wheres.Add(" A.CreateTime>=?LearnTrainSignStartDate ");
                parameters.Add("?LearnTrainSignStartDate", learnTrainSignStartDate.Value.Date, DbType.Date);
            }

            if (learnTrainSignEndDate.HasValue)
            {
                wheres.Add(" A.CreateTime<?LearnTrainSignEndDate ");
                parameters.Add("?LearnTrainSignEndDate", learnTrainSignEndDate.Value.AddDays(1).Date, DbType.Date);
            }
            if (learnTrainPassedStartDate.HasValue)
            {
                wheres.Add(" A.LearnTrainPassedTime>=?LearnTrainPassedStartDate ");
                parameters.Add("?LearnTrainPassedStartDate", learnTrainPassedStartDate.Value.Date, DbType.Date);
            }
            if (learnTrainPassedEndDate.HasValue)
            {
                wheres.Add(" A.LearnTrainPassedTime<?LearnTrainPassedEndDate ");
                parameters.Add("?LearnTrainPassedEndDate", learnTrainPassedEndDate.Value.AddDays(1).Date, DbType.Date);
            }

            string sql = string.Format(@"{0} {1} ORDER BY A.DriverLearningTrainRecordId DESC ; 
                                        SELECT COUNT(1)
                                        FROM
                                            DriverLearningTrainRecord A
                                            INNER JOIN PendingLearningVehicle B ON A.DriverId=B.DriverId
                                            INNER JOIN VehicleTrainingScheduleRecord C ON B.VehicleTrainingScheduleRecordId=C.VehicleTrainingScheduleRecordId
                                            INNER JOIN TrainingSiteConfig D ON D.TrainingSiteConfigId=C.TrainingSiteConfigId
                                            INNER JOIN driver E ON A.DriverId=E.DriverId
                                            INNER JOIN `user` F ON F.UserId=E.UserId {1}",
                   QUERY_VIEW,
                  wheres.Count > 0 ? " WHERE " + string.Join(" AND ", wheres) : "",
                  (pageIndex == 0 ? 0 : pageIndex - 1) * pageSize
                  );

            return await this.database.Slave.QueryPageableListAsync<DriverLearningTrainRecordViewEntity>(sql, parameters);
        }
        #endregion

    }
}
//接口




